package cn.xyy.jdbc.support;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import cn.xyy.common.exception.JdbcException;
import cn.xyy.common.utils.StringUtils;
import cn.xyy.jdbc.sql.SqlGenerator;
import cn.xyy.jdbc.sql.SqlProvider;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 * jdbc抽象类
 * 
 * @author xyy
 *
 */
public abstract class AbstractJdbcSupport implements IObjectOperation, ISqlOperation {
	protected abstract JdbcTemplate getJdbcTemplate();

	// ---------------------------------object---------------------------------
	@Override
	public <T> T selectById(Object id, Class<T> classz) {
		if (id == null) {
			throw new JdbcException("selectById id cannot be null");
		}
		SqlProvider sqlProvider = SqlGenerator.selectByIdSql(id, classz);
		List<T> list = getJdbcTemplate().query(sqlProvider.getSql(), sqlProvider.getParameters().toArray(),
				new BeanPropertyRowMapper<T>((Class<T>) classz));
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;

	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> T selectOne(T entity) {
		if (entity == null) {
			throw new JdbcException("selectOne entity cannot be null");
		}
		SqlProvider sqlProvider = SqlGenerator.selectSql(entity);
		List<T> list = getJdbcTemplate().query(sqlProvider.getSql(), sqlProvider.getParameters().toArray(),
				new BeanPropertyRowMapper<T>((Class<T>) entity.getClass()));
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> select(T entity) {
		if (entity == null) {
			throw new JdbcException("select entity cannot be null");
		}
		SqlProvider sqlProvider = SqlGenerator.selectSql(entity);
		return getJdbcTemplate().query(sqlProvider.getSql(), sqlProvider.getParameters().toArray(),
				new BeanPropertyRowMapper<T>((Class<T>) entity.getClass()));
	}

	@Override
	public <T> int insert(T entity) {
		if (entity == null) {
			throw new JdbcException("insert entity cannot be null");
		}
		SqlProvider sqlProvider = SqlGenerator.insertSql(entity);
		if (sqlProvider.getParameters() == null || sqlProvider.getParameters().isEmpty()) {
			throw new JdbcException("insert parameters cannot be null");
		}
		return insertOrUpdateOrDelete(sqlProvider.getSql(), sqlProvider.getParameters().toArray());
	}

	@Override
	public <T> Integer insertReturnAutoIncrement(T entity) {
		if (entity == null) {
			throw new JdbcException("insert entity cannot be null");
		}
		SqlProvider sqlProvider = SqlGenerator.insertSql(entity);
		if (sqlProvider.getParameters() == null || sqlProvider.getParameters().isEmpty()) {
			throw new JdbcException("insert parameters cannot be null");
		}
		KeyHolder keyHolder = new GeneratedKeyHolder();
		final Object[] params = sqlProvider.getParameters().toArray();
		if (params == null || params.length == 0) {
			getJdbcTemplate().update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sqlProvider.getSql(),
							PreparedStatement.RETURN_GENERATED_KEYS);
					return ps;
				}
			}, keyHolder);

		} else {
			getJdbcTemplate().update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
					PreparedStatement ps = con.prepareStatement(sqlProvider.getSql(),
							PreparedStatement.RETURN_GENERATED_KEYS);
					for (int i = 0; i < params.length; i++)
						ps.setObject(i + 1, params[i]);
					return ps;
				}
			}, keyHolder);
		}
		return keyHolder.getKey().intValue();
	}

	@Override
	public <T> int update(T entity) {
		if (entity == null) {
			throw new JdbcException("update entity cannot be null");
		}
		SqlProvider sqlProvider = SqlGenerator.updateSql(entity);
		if (sqlProvider.getParameters() == null || sqlProvider.getParameters().isEmpty()) {
			throw new JdbcException("update parameters cannot be null");
		}

		return insertOrUpdateOrDelete(sqlProvider.getSql(), sqlProvider.getParameters().toArray());
	}

	@Override
	public <T> int delete(T entity) {
		if (entity == null) {
			throw new JdbcException("delete entity cannot be null");
		}
		SqlProvider sqlProvider = SqlGenerator.deleteSql(entity);
		if (sqlProvider.getParameters() == null || sqlProvider.getParameters().isEmpty()) {
			throw new JdbcException("delete parameters cannot be null");
		}
		return insertOrUpdateOrDelete(sqlProvider.getSql(), sqlProvider.getParameters().toArray());
	}

	@Override
	public <T> int[] batchUpdate(Collection<T> collection) {
		if (collection == null || collection.isEmpty()) {
			throw new JdbcException("updateBatch collection cannot be null");
		}
		int row[] = null;
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		String sql = "";
		for (T t : collection) {
			SqlProvider sqlProvider = SqlGenerator.updateSql(t);
			if (StringUtils.isBlank(sql)) {
				sql = sqlProvider.getSql();
			}
			batchArgs.add(sqlProvider.getParameters().toArray());
		}
		if (batchArgs == null || batchArgs.isEmpty()) {
			throw new JdbcException("batchUpdate batchArgs cannot be null");
		}
		row = getJdbcTemplate().batchUpdate(sql, batchArgs);
		return row;
	}

	@Override
	public <T> int[] batchInsert(Collection<T> collection) {
		if (collection == null || collection.isEmpty()) {
			throw new JdbcException("batchInsert collection cannot be null");
		}
		int row[] = null;
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		String sql = "";
		for (T t : collection) {
			SqlProvider sqlProvider = SqlGenerator.insertSql(t);
			if (StringUtils.isBlank(sql)) {
				sql = sqlProvider.getSql();
			}
			batchArgs.add(sqlProvider.getParameters().toArray());
		}
		if (batchArgs == null || batchArgs.isEmpty()) {
			throw new JdbcException("batchInsert batchArgs cannot be null");
		}
		row = getJdbcTemplate().batchUpdate(sql, batchArgs);
		return row;
	}

	@Override
	public <T> int[] batchDelete(Collection<T> collection) {
		if (collection == null || collection.isEmpty()) {
			throw new JdbcException("batchDelete collection cannot be null");
		}
		int row[] = null;
		List<Object[]> batchArgs = new ArrayList<Object[]>();
		String sql = "";
		for (T t : collection) {
			SqlProvider sqlProvider = SqlGenerator.deleteSql(t);
			if (StringUtils.isBlank(sql)) {
				sql = sqlProvider.getSql();
			}
			batchArgs.add(sqlProvider.getParameters().toArray());
		}
		if (batchArgs == null || batchArgs.isEmpty()) {
			throw new JdbcException("batchDelete batchArgs cannot be null");
		}
		row = getJdbcTemplate().batchUpdate(sql, batchArgs);
		return row;
	}

	// ---------------------------------sql---------------------------------
	@Override
	public <T> List<T> select(String sql, Class<T> classz) {
		return select(sql, null, classz);
	}

	@Override
	public <T> List<T> select(String sql, Object[] params, Class<T> classz) {
		List<T> resultList = null;
		if (params != null && params.length > 0)
			resultList = getJdbcTemplate().query(sql, params, new BeanPropertyRowMapper<T>(classz));
		else
			// BeanPropertyRowMapper是自动映射实体类的
			resultList = getJdbcTemplate().query(sql, new BeanPropertyRowMapper<T>(classz));
		return resultList;
	}

	@Override
	public <T> T selectOne(String sql, Class<T> classz) {
		return selectOne(sql, null, classz);
	}

	@Override
	public <T> T selectOne(String sql, final Object[] params, Class<T> classz) {
		List<T> resultList = select(sql, params, classz);
		if (resultList != null && resultList.size() > 0) {
			return resultList.get(0);
		}
		return null;
	}

	@Override
	public int insertOrUpdateOrDelete(String sql, final Object[] params) {
		if (params == null || params.length == 0) {
			throw new JdbcException("insertOrUpdateOrDelete parameters cannot be null");
		}
		int num = 0;
		if (params == null || params.length == 0)
			num = getJdbcTemplate().update(sql);
		else
			num = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
				public void setValues(PreparedStatement ps) throws SQLException {
					for (int i = 0; i < params.length; i++)
						ps.setObject(i + 1, params[i]);
				}
			});
		return num;
	}
}
